{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Search for keywords in Google patents\n",
    "\n",
    "## Goal\n",
    "\n",
    "We wish to search for a list of keywords in Google patents for presence and absence. The input file contains the US patents as rows and keywords as columns. We would return a matrix filled with presence and absence after searching is completed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dependencies\n",
    "\n",
    "After searching for \"Google patent scrapers\", a few Python libraries look promising that we'll experiment here.\n",
    "- [PyPatent](https://pypi.org/project/pypatent/)\n",
    "- [google-patent-scraper](https://pypi.org/project/google-patent-scraper/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already up-to-date: google-patent-scraper in /Users/bao/miniconda3/lib/python3.7/site-packages (1.0.5)\n",
      "Requirement already up-to-date: lxml in /Users/bao/miniconda3/lib/python3.7/site-packages (4.5.0)\n",
      "Requirement already up-to-date: XlsxWriter in /Users/bao/miniconda3/lib/python3.7/site-packages (1.2.8)\n"
     ]
    }
   ],
   "source": [
    "!pip install -U google-patent-scraper lxml XlsxWriter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import re\n",
    "import os.path as op\n",
    "import sys\n",
    "from bs4 import BeautifulSoup as bs4\n",
    "from google_patent_scraper import scraper_class\n",
    "PUB = \"Publication Number\"\n",
    "\n",
    "CACHEDIR = \"_cache\"\n",
    "os.makedirs(CACHEDIR, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Publication Number</th>\n",
       "      <th>malonyl-CoA</th>\n",
       "      <th>aspartate</th>\n",
       "      <th>beta-alanine</th>\n",
       "      <th>malonate semialdehyde</th>\n",
       "      <th>malonyl-coa reductase</th>\n",
       "      <th>lactoyl-CoA</th>\n",
       "      <th>dehydratase</th>\n",
       "      <th>decarboxylase</th>\n",
       "      <th>dehydrogenase</th>\n",
       "      <th>hydrolyase</th>\n",
       "      <th>byproduct</th>\n",
       "      <th>tolerance</th>\n",
       "      <th>purification</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US20150072399A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US20150072384A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US20150064754A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US20150056684A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US20150056651A1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Publication Number  malonyl-CoA  aspartate  beta-alanine   \\\n",
       "0    US20150072399A1          NaN        NaN            NaN   \n",
       "1    US20150072384A1          NaN        NaN            NaN   \n",
       "2    US20150064754A1          NaN        NaN            NaN   \n",
       "3    US20150056684A1          NaN        NaN            NaN   \n",
       "4    US20150056651A1          NaN        NaN            NaN   \n",
       "\n",
       "   malonate semialdehyde  malonyl-coa reductase  lactoyl-CoA  dehydratase  \\\n",
       "0                    NaN                    NaN          NaN          NaN   \n",
       "1                    NaN                    NaN          NaN          NaN   \n",
       "2                    NaN                    NaN          NaN          NaN   \n",
       "3                    NaN                    NaN          NaN          NaN   \n",
       "4                    NaN                    NaN          NaN          NaN   \n",
       "\n",
       "   decarboxylase  dehydrogenase  hydrolyase  byproduct  tolerance  \\\n",
       "0            NaN            NaN         NaN        NaN        NaN   \n",
       "1            NaN            NaN         NaN        NaN        NaN   \n",
       "2            NaN            NaN         NaN        NaN        NaN   \n",
       "3            NaN            NaN         NaN        NaN        NaN   \n",
       "4            NaN            NaN         NaN        NaN        NaN   \n",
       "\n",
       "   purification  \n",
       "0           NaN  \n",
       "1           NaN  \n",
       "2           NaN  \n",
       "3           NaN  \n",
       "4           NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"data.xlsx\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Cache exists for `US20150072399A1` [1/13]\n",
      "Cache exists for `US8741624` [12/13]\n",
      "Cache exists for `US8829112` [10/13]\n",
      "Cache exists for `US20140309451A1` [9/13]\n",
      "Cache exists for `US8894762` [8/13]\n",
      "Cache exists for `US8911978` [7/13]\n",
      "Cache exists for `US20140135526A1` [13/13]\n",
      "Cache exists for `US20140230091A1` [11/13]\n",
      "Cache exists for `US20150064754A1` [3/13]\n",
      "Cache exists for `US20140377823A1` [6/13]\n",
      "Cache exists for `US20150072384A1` [2/13]\n",
      "Cache exists for `US20150056684A1` [4/13]\n",
      "Cache exists for `US20150056651A1` [5/13]\n",
      "Success! All 13 processes completed\n"
     ]
    }
   ],
   "source": [
    "from multiprocessing import Lock, Process, Queue\n",
    "\n",
    "lock = Lock() # The lock is global\n",
    "\n",
    "def worker(i, patent, total_patents, queue):\n",
    "    cache_html = op.join(CACHEDIR, \"{}.html\".format(patent))\n",
    "    progress = \"[{}/{}]\".format(i + 1, total_patents)\n",
    "    # Download webpage\n",
    "    if op.exists(cache_html):\n",
    "        message = \"Cache exists for `{}` {}\".format(patent, progress)\n",
    "        with open(cache_html) as fp: \n",
    "            soup = bs4(fp.read(), features=\"lxml\")\n",
    "    else:\n",
    "        message = \"Cache patent `{}` {}\".format(patent, progress)\n",
    "        scraper = scraper_class()\n",
    "        err, soup, url = scraper.request_single_patent(patent)\n",
    "        with open(cache_html, \"w\") as fw:\n",
    "            print(str(soup), file=fw)\n",
    "\n",
    "    with lock:\n",
    "        print(message, file=sys.stderr)        \n",
    "    \n",
    "    # Search for keywords\n",
    "    for keyword in keywords:\n",
    "        keyword_pat = re.compile(keyword.strip(), re.IGNORECASE)\n",
    "        keyword_count = len(soup.findAll(text=keyword_pat))\n",
    "        queue.put((i, keyword, keyword_count))\n",
    "\n",
    "keywords = df.columns[1:]\n",
    "total_patents = df.shape[0]\n",
    "processes = []\n",
    "results_queue = Queue()\n",
    "for i, row in df.iterrows():\n",
    "    patent = row[PUB]\n",
    "    worker(i, patent, total_patents, results_queue)\n",
    "    break\n",
    "    \n",
    "for i, row in df.iterrows():\n",
    "    if i == 0: # not sure why we need to run a non-parallel function to 'prime' this run\n",
    "        continue\n",
    "    patent = row[PUB]\n",
    "    p = Process(target=worker, args=(i, patent, total_patents, results_queue))\n",
    "    p.start()\n",
    "    processes.append(p)\n",
    "\n",
    "for p in processes:\n",
    "    p.join()\n",
    "\n",
    "while not results_queue.empty():\n",
    "    i, patent, patent_count = results_queue.get()\n",
    "    df.at[i, patent] = patent_count\n",
    "\n",
    "print(\"Success! All {} processes completed\".format(len(processes) + 1), file=sys.stderr) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Publication Number</th>\n",
       "      <th>malonyl-CoA</th>\n",
       "      <th>aspartate</th>\n",
       "      <th>beta-alanine</th>\n",
       "      <th>malonate semialdehyde</th>\n",
       "      <th>malonyl-coa reductase</th>\n",
       "      <th>lactoyl-CoA</th>\n",
       "      <th>dehydratase</th>\n",
       "      <th>decarboxylase</th>\n",
       "      <th>dehydrogenase</th>\n",
       "      <th>hydrolyase</th>\n",
       "      <th>byproduct</th>\n",
       "      <th>tolerance</th>\n",
       "      <th>purification</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US20150072399A1</td>\n",
       "      <td>14</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>113</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US20150072384A1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>60</td>\n",
       "      <td>35</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US20150064754A1</td>\n",
       "      <td>76</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>27</td>\n",
       "      <td>52</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>97</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US20150056684A1</td>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>20</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>178</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US20150056651A1</td>\n",
       "      <td>207</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>15</td>\n",
       "      <td>36</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>32</td>\n",
       "      <td>59</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>161</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Publication Number  malonyl-CoA  aspartate  beta-alanine   \\\n",
       "0    US20150072399A1           14          4              5   \n",
       "1    US20150072384A1            0          0              5   \n",
       "2    US20150064754A1           76          4              3   \n",
       "3    US20150056684A1           14          7              8   \n",
       "4    US20150056651A1          207          9              9   \n",
       "\n",
       "   malonate semialdehyde  malonyl-coa reductase  lactoyl-CoA  dehydratase  \\\n",
       "0                     10                      8            0            4   \n",
       "1                     12                      0            0            1   \n",
       "2                     27                     52            0            0   \n",
       "3                      9                      9            0            1   \n",
       "4                     15                     36            0            7   \n",
       "\n",
       "   decarboxylase  dehydrogenase  hydrolyase  byproduct  tolerance  \\\n",
       "0              7            113           0          0         10   \n",
       "1             60             35           0          1         32   \n",
       "2              6             97           0          2          8   \n",
       "3             20             29           0          0        178   \n",
       "4             32             59           0          2        161   \n",
       "\n",
       "   purification  \n",
       "0            19  \n",
       "1            22  \n",
       "2             5  \n",
       "3            16  \n",
       "4            30  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mf = df.astype(dict((keyword, \"int32\") for keyword in keywords))\n",
    "mf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Success! Output written to `export.xlsx`\n"
     ]
    }
   ],
   "source": [
    "output = \"export.xlsx\"\n",
    "writer = pd.ExcelWriter(output, engine=\"xlsxwriter\")\n",
    "mf.to_excel(writer, sheet_name=\"Sheet1\", startrow=1, header=False, index=False)\n",
    "\n",
    "# Styling\n",
    "workbook = writer.book\n",
    "worksheet = writer.sheets[\"Sheet1\"]\n",
    "\n",
    "header_format = workbook.add_format({\n",
    "    'bold': True,\n",
    "    'valign': 'top',\n",
    "    'fg_color': '#D7E4BC',\n",
    "    'border': 1})\n",
    "\n",
    "link_format = workbook.add_format({\n",
    "    'font_color': 'red',\n",
    "    'bold':       1,\n",
    "    'underline':  1,\n",
    "})\n",
    "\n",
    "# Write the column headers with the defined format\n",
    "for col_num, value in enumerate(df.columns.values):\n",
    "    worksheet.write(0, col_num, value, header_format)\n",
    "\n",
    "def make_hyperlink(value):\n",
    "    url = \"https://patents.google.com/patent/{}\"\n",
    "    return url.format(value)\n",
    "    \n",
    "# Format URL link\n",
    "for row_num, value in enumerate(df[PUB]):\n",
    "    worksheet.write_url(row_num + 1, 0, make_hyperlink(value), link_format, string=value)\n",
    "\n",
    "# Close the Pandas Excel writer and output the Excel file.\n",
    "writer.save()\n",
    "\n",
    "print(\"Success! Output written to `{}`\".format(output), file=sys.stderr)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
